package cn.kgc.gmall.mapper;

import cn.kgc.gmall.pojo.ProductStats;
import cn.kgc.gmall.pojo.ProvinceStats;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface PublisherMapper {
    // date = yyyyMMdd
    @Select("select sum(order_amount) from product_stats_2022 where toYYYYMMDD(stt) = #{date}")
    BigDecimal getGMV(Integer date);

    @Select("select tm_id,tm_name,sum(order_amount) order_amount  from product_stats_2022 where toYYYYMMDD(stt) = #{date} " +
            "group by tm_id,tm_name having order_amount > 0 order by order_amount desc limit #{limit}")
    List<ProductStats> getTrademark(Integer date, Integer limit);

    @Select("select spu_id,spu_name,sum(order_ct) order_ct,sum(order_amount) order_amount from product_stats_2022 where toYYYYMMDD(stt) = #{date} " +
            "group by spu_id,spu_name having order_amount > 0 order by order_amount desc limit #{limit} ")
    List<ProductStats> getSPU(Integer date, Integer limit);

    @Select("select category3_id,category3_name,sum(order_amount) order_amount from product_stats_2022 where toYYYYMMDD(stt) = #{date} " +
            "group by category3_id,category3_name having order_amount > 0 order by order_amount desc ")
    List<ProductStats> getCategory3(Integer date);

    @Select("select province_id,province_name,sum(order_amount) order_amount from province_stats_2022 where toYYYYMMDD(stt) = #{date}" +
            " group by province_id,province_name ")
    List<ProvinceStats> getProvince(Integer date);
}
